package god.dream.user.dao;

import god.dream.user.model.User;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

public class SpringJdbcUserDao extends SimpleJdbcDaoSupport implements UserDAO {
	public int insert(User user) throws DataAccessException {
		String query = "INSERT INTO "
				+ "USER ( U_ID, U_NAME, U_AGE, U_BIRDATE, U_PHONE, U_CELLPHONE, "
				+ "U_DEP, U_CAREER, U_EMAIL, U_ADDR, U_NOTE ) "
				+ "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		return getSimpleJdbcTemplate().update(query, user.insert());
	}

	public int update(User user) throws DataAccessException {
		String query = "UPDATE USER "
				+ "SET U_NAME=?, U_AGE=?, U_BIRDATE=?,"
				+ "U_PHONE=?, U_CELLPHONE=?, U_DEP=?, U_CAREER=?, U_EMAIL=?, U_ADDR=?, U_NOTE=? "
				+ "WHERE U_ID=?";
		return getSimpleJdbcTemplate().update(query, user.update());
	}

	public int delete(int id) throws DataAccessException {
		String query = "DELETE FROM USER WHERE U_ID=?";
		return getSimpleJdbcTemplate().update(query, new Object[] { id });
	}

	public User findUser(int id) throws DataAccessException {
		String query = "SELECT * " + "FROM USER WHERE U_ID=?";
		ParameterizedRowMapper<User> rowMapper = getRowMapper();

		try {
			return getSimpleJdbcTemplate().queryForObject(query, rowMapper,
					new Object[] { id });
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public User findUserWithName(String name) throws DataAccessException {
		String query = "SELECT * " + "FROM USER WHERE U_NAME=?";
		ParameterizedRowMapper<User> rowMapper = getRowMapper();

		try {
			return getSimpleJdbcTemplate().queryForObject(query, rowMapper,
					new Object[] { name });
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
	public List<User> findUserList() throws DataAccessException {
		String sql = "SELECT * FROM USER";
		ParameterizedRowMapper<User> rowMapper = getRowMapper();

		return getSimpleJdbcTemplate().query(sql, rowMapper);
	}	

	private ParameterizedRowMapper<User> getRowMapper() {
		ParameterizedRowMapper<User> rowMapper = new ParameterizedRowMapper<User>() {
			public User mapRow(ResultSet rs, int rownum) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("U_ID"));
				user.setName(rs.getString("U_name"));
				user.setAge(rs.getInt("U_age"));
				Date tmpDate = rs.getDate("U_birdate");
				if(tmpDate != null)
					user.setBirthDate(tmpDate.toString());
				user.setPhone(rs.getString("U_phone"));
				user.setCellPhone(rs.getString("U_cellphone"));
				user.setDepart(rs.getString("U_dep"));
				user.setCareer(rs.getString("U_career"));
				user.setEmail(rs.getString("U_email"));
				user.setAddr(rs.getString("U_addr"));
				user.setNote(rs.getString("U_note"));

				return user;
			}
		};
		return rowMapper;
	}

}
